{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  一、案例简介\n",
    "## 1. 案例背景\n",
    ">   随着时代的进步，航空航天作为我国的战略性发展事业，取得了极大成就。航天事业的发展核心是飞行安全，飞行安全既是飞行人员与乘客的生命安全保障，又是航天科技发展的方向和目标。气象条件对飞行的影响是不同的，也是不可避免的，航天部门应对飞行威胁较大的恶劣天气进行分析，采取相应的对策进行防范，不断提高飞行的安全性与可靠性。\n",
    ">   恶劣天气的类型很多，主要包括大风、云、雷电、暴雨、冰雪，另外，大雾、风切变、冰高空急流等也会对飞行安全产生不同程度的影响。恶劣天气的影响具有不确定性，因而应减少在恶劣天气范围内进行飞行。\n",
    ">   通过天气因素上座数量的分析，预测不同天气情况下航班的上座情况，进而调整飞行计划以应对不同天气带来的影响。\n",
    "\n",
    "## 2. 案例意义\n",
    ">   从分析结果将使航空公司依据天气模式调整飞行时间表。它也可以引导乘客做出新的选择。航空公司可以提前几天预测到未来航班上座情况，然后未雨绸缪地进行调整。航空公司也可以预先发出警告更有效分配自己的资源、地勤人员、飞行人员和其他资产以减少损失\n",
    "## 3. 业务图\n",
    "![业务图](image-001.png)\n",
    "## 4. 案例目标\n",
    ">     现在，大多数航班只能对连续进行重复，一般处理天气延误的航班是在其发生之后。我们的大数据能够让他们预先预测延迟，以更好地与乘客沟通，以及优化资源。通过数据分析，预测假设航班和不同天气情况之间的结果，帮助迅速提前发现因天气发生的上座影响。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 二、相关技术\n",
    "- ## 数据采集，通过pymysql连接mysql数据库获取数据 \n",
    "- ## 数据预处理，通过pandas、sklearn进行数据清洗，转换等预处理\n",
    "- ## 数据探索，通过matplotlib及pyecharts进行数据可视化，发现数据规律\n",
    "- ## 数据分析建模，通过机器学习算法库sklearn学习已有数据规律建模，预测指定航班在不同天气下的上座率"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 三、案例步骤"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ## 1. 数据采集: 从数据库casepro中获取数据表t_plane_order\\t_plane_weather,将表格转存在本地csv并展示数据\n",
    "\n",
    "> 数据库信息： host：10.102.52.248，port=3306， user=\"root\", passwd=\"root\"\n",
    "\n",
    "> 航班订单数据，包含航班时刻表，航班号，子订单，飞行日期，头等舱，公务舱，经济舱，其他，头等舱总数，公务舱总数，经济舱总数，其他总数。\n",
    "\n",
    "> 天气数据，包含基线：日期，高温，低温，天气状况，风，空气"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql \n",
    "import csv   \n",
    "import pandas as pd\n",
    "conn = pymysql.connect(host='10.102.52.248',user='root',password='root',database='casepro',charset='utf8')\n",
    "weather = \"select * from t_plane_weather;\"\n",
    "order = \"select * from t_plane_order;\"\n",
    "df1 = pd.read_sql(weather,conn)\n",
    "df2 = pd.read_sql(order,conn)\n",
    "df1.to_csv(path_or_buf=\"./t_plane_weather.csv\",float_format=8)\n",
    "df2.to_csv(path_or_buf=\"./t_plane_order.csv\",float_format=8)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "# 数据信息,根据实际情况修改\n",
    "DBIP = \"10.102.52.248\"\n",
    "DBPORT = 3306\n",
    "DBUSER = \"root\"\n",
    "DBPASSWD = \"root\"\n",
    "DBNAME = \"casepro\"\n",
    "# 连接数据库\n",
    "def getConn():\n",
    "    conn = pymysql.connect(host=DBIP, port=DBPORT, user=DBUSER, passwd=DBPASSWD, db=DBNAME)\n",
    "    return conn\n",
    "# 关闭数据库\n",
    "def closeConn(conn):\n",
    "    conn.close\n",
    "# 写入文件\n",
    "def getDate(conn,sqlStr,fileName,headMsg):\n",
    "    with open(fileName, \"w\", encoding=\"utf-8\") as f:\n",
    "        # 写入字段标题\n",
    "        f.write(headMsg)\n",
    "        # 获取mysql数据\n",
    "        cursor = conn.cursor()\n",
    "        cursor.execute(sqlStr)\n",
    "        rows = cursor.fetchall()\n",
    "        # 循环数据内容\n",
    "        for row in rows:\n",
    "            # 以逗号分割数据写入文件\n",
    "            f.write(\",\".join(row).strip() + \"\\n\")\n",
    "    cursor.close()\n",
    "\n",
    "conn = getConn()\n",
    "# 输出文件目录(自己的路径)\n",
    "filePath = \"./\"\n",
    "# 定义写入文件\n",
    "fileName = filePath + \"planeorder.csv\"\n",
    "# 设置字段信息\n",
    "headMsg = \"序号,航班号,子订单,飞行日期,头等舱,公务舱,经济舱,其他,头等舱总数,公务舱总数,经济舱总数,其他总数\\n\"\n",
    "# 查询数据表\n",
    "goodsSql = \"select * from t_plane_order\"\n",
    "# 调用获取数据库数据并写入文件方法\n",
    "getDate(conn,goodsSql,fileName,headMsg)\n",
    "# 定义写入文件\n",
    "fileName = filePath + \"plan_weather.csv\"\n",
    "# 设置字段信息\n",
    "headMsg = \"日期,高温,低温,天气状况,风,空气\\n\"\n",
    "saleListSql = \"select * from t_plane_weather\"\n",
    "# 调用获取数据库数据并写入文件方法\n",
    "getDate(conn,saleListSql,fileName,headMsg)\n",
    "closeConn(conn)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ## 2、数据预处理（清洗、数值化转换、标准化等）\n",
    "\n",
    "> 空数据判断和处理（注意分析空占比，区分数值型和类别型）\n",
    "\n",
    "> 数据规范性检查（格式、范围等）\n",
    "\n",
    "> 去除不规范数据、无效、无分析价值数据等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\python\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3058: DtypeWarning: Columns (3,5) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Unnamed: 0            0\n",
       "orgId                 0\n",
       "plane_code            0\n",
       "children_order        0\n",
       "plane_date            0\n",
       "first_class           0\n",
       "business_class        0\n",
       "economy_class         0\n",
       "other_class           0\n",
       "first_class_all       0\n",
       "business_class_all    0\n",
       "economy_class_all     0\n",
       "other_class_all       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('t_plane_order.csv')\n",
    "df.isnull().sum()\n",
    "#print(df[df['plane_code'].isna().values==True])\n",
    "df2 = df.dropna(axis=0,how='any',thresh=None,subset=None,inplace=False)\n",
    "df2.isnull().sum() \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ## 3、数据探索，发现数据中的规律"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ## 4、数据分析建模并评估"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- ## 5、整理工程并部署：输入航班号、根据未来一周的天气（网上爬取的天气预报），预测并输出其上座率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import urllib.request\n",
    "from  bs4 import BeautifulSoup      ## 引入解析模块BS4\n",
    "url = \"http://www.weather.com.cn/weather/101270101.shtml\"\n",
    "header = (\"User-Agent\",\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36\")  # 设置头部信息\n",
    "opener = urllib.request.build_opener()  # 修改头部信息\n",
    "opener.addheaders = [header]         #修改头部信息\n",
    "request = urllib.request.Request(url)   # 制作请求\n",
    "response = urllib.request.urlopen(request)   #  得到请求的应答包\n",
    "html = response.read()   #将应答包里面的内容读取出来\n",
    "html = html.decode('utf-8')    # 使用utf-8进行编码，不重新编码就会成乱码\n",
    "# 以上部分的代码如下：\n",
    "final = []   #初始化一个空的list，将最终的的数据保存到list\n",
    "bs = BeautifulSoup(html,\"html.parser\")   # 创建BeautifulSoup对象\n",
    "body = bs.body  # 获取body部分\n",
    "data = body.find('div',{'id':'7d'})  # 找到id为7d的div\n",
    "ul = data.find('ul')  # 获取ul部分，由于ul标签只有一个  我们使用find()函数，如果有多个我们使用find_all()\n",
    "li = ul.find_all('li')  # 获取所有的li     返回的是list对象\n",
    "i = 0\n",
    "for day in li:  # 对每个li标签中的内容进行遍历\n",
    "    if i < 7:\n",
    "        temp = []\n",
    "        date = day.find('h1').string # 找到日期\n",
    "#         print (date)\n",
    "        temp.append(date)  # 添加到temp中\n",
    "    #     print (temp)\n",
    "        inf = day.find_all('p')  # 找到li中的所有p标签\n",
    "    #     print(inf)\n",
    "    #     print (inf[0])\n",
    "        temp.append(inf[0].string)  # 第一个p标签中的内容（天气状况）加到temp中\n",
    "        if inf[1].find('span') is None:\n",
    "            temperature_highest = None # 天气预报可能没有当天的最高气温（到了傍晚，就是这样），需要加个判断语句,来输出最低气温\n",
    "        else:\n",
    "            temperature_highest = inf[1].find('span').string # 找到最高温度\n",
    "            temperature_highest = temperature_highest.replace('℃', '') # 到了晚上网站会变，最高温度后面也有个℃\n",
    "        temperature_lowest = inf[1].find('i').string  #找到最低温度\n",
    "        temperature_lowest = temperature_lowest.replace('℃', '')  # # 最低温度后面有个℃，去掉这个符号\n",
    "        temp.append(temperature_highest)\n",
    "        temp.append(temperature_lowest)\n",
    "        final.append(temp)  # 将每一次循环的list的内容都插入最后保存数据的list\n",
    "        i = i +1\n",
    "with open('weather.csv', 'a', errors='ignore', newline='') as f:\n",
    "            f_csv = csv.writer(f)\n",
    "            f_csv.writerows(final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
